USE [ServerMonitor]
GO
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[AllServerDBSizeMonitor]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[AllServerDBSizeMonitor]
GO
USE [ServerMonitor]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[AllServerDBSizeMonitor]
AS
BEGIN
  SET NOCOUNT ON
  DECLARE @Key NVARCHAR(256) = 'lenovo,2008'
  DECLARE ServerCur CURSOR LOCAL LOCAL READ_ONLY
  FOR
    SELECT [Id],[IP],
    CAST(DecryptByPassphrase(@Key,[UserName]) AS NVARCHAR(256)),
    CAST(DecryptByPassphrase(@Key,[Password]) AS NVARCHAR(256))
    FROM [ServerMonitor].[dbo].[Server]
    ORDER BY [Id]
  DECLARE @ServerId INT, @IP VARCHAR(50), @User SYSNAME, @PW SYSNAME
  OPEN ServerCur
  FETCH NEXT FROM ServerCur INTO @ServerId,@IP,@User,@PW
  WHILE @@FETCH_STATUS = 0
  BEGIN    
    DECLARE @DBTable TABLE(Id INT,DBName NVARCHAR(256),DataSize INT,DataUsage INT,
    LogSize INT,LogUsage INT,RecordTime DATETIME)
    DECLARE @query NVARCHAR(MAX) = REPLACE('
    SELECT [Id],[DBName],[DataSize],[DataUsage],[LogSize],[LogUsage],[RecordTime]
    FROM OPENDATASOURCE(''SQLOLEDB'',''DATA SOURCE=#SourceIP#;USER ID=#User#;PASSWORD=#PW#'').
    [ServerMonitor].[dbo].[DBUsage]
    WHERE DATEDIFF(DAY,[RecordTime],GETDATE()) <= 7 ','#SourceIP#',@IP)
    SET @query = REPLACE(@query,'#User#',@User)
    SET @query = REPLACE(@query,'#PW#',@PW)
    INSERT INTO @DBTable
    EXEC SP_EXECUTESQL @query  
  
    MERGE INTO [ServerMonitor].[dbo].[AllServerDBUsage] AS Target
    USING (SELECT [Id],@ServerId AS ServerId,[DBName],[DataSize],[DataUsage],[LogSize],[LogUsage],[RecordTime] FROM @DBTable)
    AS Source([Id],[ServerId],[DBName],[DataSize],[DataUsage],[LogSize],[LogUsage],[RecordTime])
    ON Target.[Id] = Source.[Id] AND Target.[ServerId] = Source.[ServerId]
    WHEN MATCHED THEN 
    UPDATE SET [DBName] = Source.[DBName],[DataSize] = Source.[DataSize],[DataUsage] = Source.[DataUsage],
    [LogSize] = Source.[LogSize],[LogUsage] = Source.[LogUsage]
    WHEN NOT MATCHED THEN
    INSERT ([Id],[ServerId],[DBName],[DataSize],[DataUsage],[LogSize],[LogUsage],[RecordTime])
    VALUES(Source.[Id],Source.[ServerId],Source.[DBName],Source.[DataSize],Source.[DataUsage],
    Source.[LogSize],Source.[LogUsage],Source.[RecordTime]);
    
    DECLARE @MinId INT,@MaxId INT
    SELECT @MinId = MIN(Id),@MaxId = MAX(Id) FROM @DBTable
    
    DELETE FROM @DBTable  
    
    DECLARE @FileTable TABLE(Id INT,DBUsageId INT,DBFileId INT,name NVARCHAR(256),
    FileType CHAR(50),FilePath NVARCHAR(256),DiskFreeMB INT)
    SET @query = REPLACE('
    SELECT [Id],[DBUsageId],[DBFileId],[name],[FileType],[FilePath],[DiskFreeMB]
    FROM OPENDATASOURCE(''SQLOLEDB'',''DATA SOURCE=#SourceIP#;USER ID=#User#;PASSWORD=#PW#'').
    [ServerMonitor].[dbo].[DBFile]
    WHERE [DBUsageId] >= #MinId# AND [DBUsageId] <= #MaxId#','#SourceIP#',@IP)
    SET @query = REPLACE(@query,'#User#',@User)
    SET @query = REPLACE(@query,'#PW#',@PW)
    SET @query = REPLACE(@query,'#MinId#',CONVERT(VARCHAR(9),@MinId))
    SET @query = REPLACE(@query,'#MaxId#',CONVERT(VARCHAR(9),@MaxId))
    INSERT INTO @FileTable
    EXEC SP_EXECUTESQL @query  
    
    MERGE INTO [ServerMonitor].[dbo].[AllServerDBFile] AS Target
    USING (SELECT [Id],@ServerId AS ServerId,[DBUsageId],[DBFileId],[name],[FileType],[FilePath],[DiskFreeMB] FROM @FileTable)
    AS Source([Id],[ServerId],[DBUsageId],[DBFileId],[name],[FileType],[FilePath],[DiskFreeMB])
    ON Target.[Id] = Source.[Id] AND Target.[ServerId] = Source.[ServerId] AND Target.[DBUsageId] = Source.[DBUsageId] AND Target.[DBFileId] = Source.[DBFileId]
    WHEN MATCHED THEN 
    UPDATE SET [DBFileId] = Source.[DBFileId],[name] = Source.[name],[FileType] = Source.[FileType],
    [FilePath] = Source.[FilePath],[DiskFreeMB] = Source.[DiskFreeMB]
    WHEN NOT MATCHED THEN
    INSERT ([Id],[ServerId],[DBUsageId],[DBFileId],[name],[FileType],[FilePath],[DiskFreeMB])
    VALUES(Source.[Id],Source.[ServerId],Source.[DBUsageId],Source.[DBFileId],Source.[name],
    Source.[FileType],Source.[FilePath],Source.[DiskFreeMB]);   
    
    DELETE FROM @FileTable
  
    FETCH NEXT FROM ServerCur INTO @ServerId,@IP,@User,@PW
  END
  CLOSE ServerCur
  DEALLOCATE ServerCur  
  
  SET NOCOUNT OFF 
END
GO